/**
 * 
 */
package com.save_file_for_postgresql;

import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author Administrator
 *  TODO 此示例不仅仅可以存储图片任何格式的文件都可以存储，但是需要注意内存溢出
 *
 * 使用PostgreSql数据库存储图片示例，真实情况下需要存储后缀名
 *
 *
 * PostgreSql数据库DDL
 create table image
 (
   id         serial                                     not null
     constraint pk_notice_image
       primary key,
   image_name varchar(256) default ''::character varying not null,
   image_file bytea
 );
 *
 */
public class PostgreSQLSaveFileDemo {

    // 将图片插入数据库
    public static void save() {
        String path = "D:\\apache-activemq-5.15.11.zip";//需要读取的图片全路径
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        FileInputStream fileInputStream = null;
        try {
            fileInputStream = FileUtilForPostgreSQL.readImage(path);
            connection = PostgreSQLUtils.getConn();
            String sql = "insert into image (id,image_name,image_file)values(?,?,?)";
            preparedStatement = connection.prepareStatement(sql);//预编译SQL
            //给Sql赋值数据
            preparedStatement.setInt(1, 2);
            preparedStatement.setString(2, "zip");
            preparedStatement.setBinaryStream(3, fileInputStream/*赋值输入流*/, fileInputStream.available()/*返回字节数*/);
            int count = preparedStatement.executeUpdate();
            if (count > 0) {
                System.out.println("插入成功！");
            } else {
                System.out.println("插入失败！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            PostgreSQLUtils.closeConn(connection);
            if (null != preparedStatement) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }

    // 读取数据库中图片
    public static void read() {
        String targetPath = "C:\\Users\\zhangyue26\\Desktop\\apache-activemq-5.15.11.zip";//读取到目标路径
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = PostgreSQLUtils.getConn();
            String sql = "select * from image where id =?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, 2);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                InputStream in = resultSet.getBinaryStream("image_file");
                FileUtilForPostgreSQL.readBin2Image(in, targetPath);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            PostgreSQLUtils.closeConn(connection);
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }
    }
    //测试
    public static void main(String[] args) {
        save();
//        read();
    }
}